package com.happysmile.ipblock.utils;

import com.happysmile.ipblock.config.MysqlConfig;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;

@Slf4j
public class JdbcHelper {
    public static Connection getConnection() throws SQLException {
        Connection conn = null;
        String driver = "com.mysql.cj.jdbc.Driver";// 驱动	        　　　　　　
        String url = "jdbc:mysql://" + MysqlConfig.DB_HOST + ":3306/" + MysqlConfig.DB_NAME + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8";
        String username = MysqlConfig.DB_USER;// 用户名
        String password = MysqlConfig.DB_PD;// 密码
        try {
            Class.forName(driver);// 加载驱动类
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            System.out.println("找不到驱动程序类 ，加载驱动失败！");
            e.printStackTrace();
        } catch (SQLSyntaxErrorException e) {
            url = "jdbc:mysql://" + MysqlConfig.DB_HOST + ":3306/?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8";
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            System.out.println("数据库连接失败！");
            e.printStackTrace();
        }
        // System.out.println("连接成功");
        return conn;
    }

    public static int execUpdate(String sql){
        Connection conn=null;
        Statement stmt=null;
        int rowCount=0;//定义数据集ResultSet 接受stmt.executeQuery(sql)的返回值
        try{
            conn=getConnection();
            stmt=conn.createStatement();
            rowCount=stmt.executeUpdate(sql);
            log.info(sql);
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        finally{
            closeStatement(stmt);//关闭
            closeConn(conn);//关闭数据库连接
        }
        return rowCount;
    }

    public static String getQueryStringValue(String sql){
        Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;//定义数据集ResultSet 接受stmt.executeQuery(sql)的返回值
        String  str = "";
        try{
            conn=getConnection();
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            while(rs != null  ){
                str = rs.getString(0);
            }
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        finally{
            //closeResultSet(rs);//关闭结果集
            closeStatement(stmt);//关闭
            closeConn(conn);//关闭数据库连接
        }
        return str;
    }

    public static int getQueryCount(String sql){
        Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;//定义数据集ResultSet 接受stmt.executeQuery(sql)的返回值
        int rowCount = 0;
        try{
            conn=getConnection();
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            while(rs != null && rs.next() ){
                rowCount ++;
            }
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        finally{
            //closeResultSet(rs);//关闭结果集
            closeStatement(stmt);//关闭
            closeConn(conn);//关闭数据库连接
        }
        return rowCount;
    }

    public static PreparedStatement setPreparedStatement(PreparedStatement pst, Object... objs) throws SQLException {
        String typeName;
        if (objs != null && objs.length > 0) {
            int i = 1;
            for (Object obj : objs) {
                if (null == obj) {
                    pst.setNull(i, Types.VARCHAR);
                } else {
                    typeName = TypeToolsGenerics.getType(obj);
                    if (typeName.equals("string")) {
                        pst.setString(i, null == obj ? "" : obj.toString());
                    } else if (typeName.equals("int")) {
                        pst.setInt(i, Integer.parseInt(obj.toString()));
                    } else if (typeName.equals("long")) {
                        pst.setLong(i, Long.parseLong(obj.toString()));
                    } else if (typeName.equals("float")) {
                        pst.setFloat(i, Float.parseFloat(obj.toString()));
                    } else if (typeName.equals("double")) {
                        pst.setDouble(i, Double.parseDouble(obj.toString()));
                    }
                }
                i++;
            }
        }
        return pst;
    }

    public static int getQueryCount(String sql, Object... objs){
        Connection conn=null;
        PreparedStatement pst = null;
        ResultSet rs=null;//定义数据集ResultSet 接受stmt.executeQuery(sql)的返回值
        int rowCount = 0;
        try{
            conn=getConnection();
            pst = conn.prepareStatement(sql);
            pst = setPreparedStatement(pst, objs);
            rs=pst.executeQuery(sql);
            while(rs != null  ){
                rowCount ++;
            }
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        finally{
            //closeResultSet(rs);//关闭结果集
            closePreparedStatement(pst);//关闭
            closeConn(conn);//关闭数据库连接
        }
        return rowCount;
    }
    //关闭连接
    public static void closeConn(Connection conn){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    //关闭执行对象
    public static void closePreparedStatement(PreparedStatement stmt){
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    //关闭执行对象
    public static void closeStatement(Statement stmt){
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    //关闭结果集
    public static void closeResultSet(ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}


